Aggregate Functions

NSQL statements do not always return a Domain of records. There are a series of statistical functions and aggregations that can be used on the numeric columns of INTEGER, BIGINT and REAL that will return a single value, for example:

SELECT Avg(Age) FROM Customer;

This query will simply return a single textual result of "39.90909".

For the purposes of these functions Null data is not included. To include Null data with a default value ( say 0 ) use the expression field:

IF( IsNull(<column>),0,<column>)

To generate a new column, and the functions performed on that column.

Available aggregation functions:

COUNT - The number of non-NULL values.

SUM - The sum of these values.

AVG - The average of these values.

MEAN - The same as AVG.

MIN - The minimum value in the column.

MAX - The maximum value in the column.

MODE - The value that occurs most often in a column.

MEDIAN - The middle value of the column.

STDEV - The population standard deviation.

STDEVS - The sample standard deviation of a column.

SKEW - A measure of the skew of a column.

KURTOSIS - A measure of the kurtosis of a column

FIRSTQUARTILE:

Is the value at the first quartile of the column.

THIRDQUARTILE:

Is the value at the third quartile of the column.

For more information on these statistical measures, see the section on IStatsObject in the Engine Interface Guide.

These can also be used with a WHERE clause to reduce the records that are included in the calculations, for example:

SELECT AVG(Balance)

FROM CurrentAccount

WHERE Balance >= 0;

	Online & Instructor-Led Courses \| Training Videos \| Webinar Recordings
	© Alterian. All Rights Reserved. \| Privacy Policy \| Legal Notice